﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using Microsoft.Office.Interop.Excel;
using System.Data;
using SmartMD.DataAccess.Service;
using SmartMD.DataModel.Entity;
using System.Collections.ObjectModel;
using System.Windows.Controls;
using Microsoft.Office.Interop.Excel;
using System.Collections;

namespace SmartMD.Manager.Report
{
    public class ExcelHelper
    {
        public static void ExportExcel(string filePath, List<string> patientIds)
        {
            Dictionary<string, int> dictIndiceClmNo = new Dictionary<string, int>();
            List<string> itemIdList = new List<string>();
            
            //列数
            int columnNum = 1;
            //行数
            int rowNum = 1;
            int startindex = 1;
            int endindex = 1;
            string[] _patientInfoColumns = { "序号","姓名", "性别", "年龄","出生日期","地域", "门诊号", "住院号","病理号","随访号", "联系地址", "联系电话1","联系电话2","电子邮箱", "身高（m）","体重（kg）", "体重指数"};
            string[] _followupInfoColumns = { "疾病诊断","随访时间", "随访周次", "随访人"};

            string[] _checkInfoColums = { "简要病史", "血压（mmHg）", "心率（bpm）", "呼吸（bmp）", "体温（℃）", "治疗情况", "其他问题", "病理诊断", "病理分类", "病理分级","病理分期" };
            string[] _examInfoColumns = { };

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                return;
            }
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            //填充表头
            startindex = 1;
            endindex = startindex + _patientInfoColumns.Length - 1;
            AddMergedTableHeader(worksheet, "患者信息", startindex, endindex, 1);
            for (int k = 0; k < _patientInfoColumns.Length; k++)
            {
                Range rg = worksheet.get_Range(CharUtil.GetExcelCharByNumber(k + 1) + "2", CharUtil.GetExcelCharByNumber(k + 1) + "4");
                rg.Merge(0);
                rg.Cells[1, 1] = _patientInfoColumns[k];
                rg.Interior.ColorIndex = 20;
                rg.Font.Bold = true;
            }
           

            startindex = _patientInfoColumns.Length + 1;
            for (int j = 0; j < _followupInfoColumns.Length; j++)
            {
                Range rg = worksheet.get_Range(CharUtil.GetExcelCharByNumber(startindex + j) + "2", CharUtil.GetExcelCharByNumber(startindex + j) + "4");
                rg.Merge(0);
                rg.Cells[1, 1] = _followupInfoColumns[j];             
                rg.Interior.ColorIndex = 20;
                rg.Font.Bold = true;
                
            }
            startindex = _followupInfoColumns.Length + _patientInfoColumns.Length + 1;
            endindex = startindex + _checkInfoColums.Length - 1;
            AddMergedTableHeader(worksheet, "诊断信息", startindex, endindex, 2);

            startindex = _patientInfoColumns.Length + _followupInfoColumns.Length + 1;
            for (int j = 0; j < _checkInfoColums.Length; j++)
            {
                worksheet.Cells[3, j + startindex] = _checkInfoColums[j];
                Range rg = worksheet.Cells[3, j + startindex] as Range;
                rg.Interior.ColorIndex = 20;
                rg.Font.Bold = true;

            }
           
            startindex = _followupInfoColumns.Length + _patientInfoColumns.Length + _checkInfoColums.Length + 1;
            ObservableCollection<ExamItemInfo> examItemInfos = DataServiceManager.ExamItemService.loadAllExamItemInfo(true);
            for (int j = 0; j < examItemInfos.Count; j++)
            {
                itemIdList.Add(examItemInfos[j].Id);
                int columnStartNo = startindex;
                startindex += examItemInfos[j].columns.Count;
                int columnEndNo = startindex - 1;
                AddMergedTableHeader(worksheet, examItemInfos[j].Name, columnStartNo, columnEndNo, 3);
         
                ObservableCollection<IndiceDefine> indiceDefines = examItemInfos[j].columns;
                for (int i = 0; i < indiceDefines.Count; i++)
                {
                    AddMergedTableHeader(worksheet, indiceDefines[i].Name, columnStartNo + i, columnStartNo + i, 4);
                    string key = examItemInfos[j].Id + indiceDefines[i].Name;
                    int value = columnStartNo + i;
                    if (!dictIndiceClmNo.ContainsKey(key))
                    {
                        dictIndiceClmNo.Add(key, value);
                    }
                }
            }
            int indiceNum = 0;
            for (int i = 0; i < examItemInfos.Count; i++)
            {
                indiceNum += examItemInfos[i].columns.Count;
            }
            startindex = _followupInfoColumns.Length + _patientInfoColumns.Length + _checkInfoColums.Length + 1;
            endindex = startindex + indiceNum - 1;
            AddMergedTableHeader(worksheet, "检验检查", startindex, endindex, 2);

            startindex = _patientInfoColumns.Length + 1;
            endindex = startindex + _followupInfoColumns.Length + _checkInfoColums.Length + _examInfoColumns.Length + indiceNum - 1;
            AddMergedTableHeader(worksheet, "随访记录", startindex, endindex, 1);
            //填充内容
            rowNum = 5;
            int no = 1;
            ObservableCollection<PatientInfo> pInfoList = DataServiceManager.PatientInfoService.loadAllPatientInfoByIds(patientIds);
            if (pInfoList == null)
                return;
          for (int j = 0; j < pInfoList.Count; j++)
          {
              PatientInfo pInfo = pInfoList[j];
              ObservableCollection<FollowupInfo> followupInfoList = DataServiceManager.FollowupInfoService.GetFollowupInfoByPatientId(pInfo.Id);
              if (followupInfoList == null || followupInfoList.Count <= 0)
                  continue;
              Range rg2 = null;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(1) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(1) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = no.ToString();
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(2) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(2) + (followupInfoList.Count -1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Name;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(3) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(3) + (followupInfoList.Count -1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Sex;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(4) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(4) + (followupInfoList.Count -1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Age;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(5) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(5) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Birthday;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(6) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(6) + (followupInfoList.Count -1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Area;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(7) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(7) + (followupInfoList.Count -1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.ClinicNo;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(8) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(8) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.HospitalNo;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(9) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(9) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.PathologyNo;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(10) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(10) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.FollowUpNo;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(11) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(11) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Address;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(12) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(12) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Telephone;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(13) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(13) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Telephone2;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(14) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(14) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Email;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(15) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(15) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Height;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(16) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(16) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = pInfo.Weight;
              rg2 = worksheet.get_Range(CharUtil.GetExcelCharByNumber(17) + rowNum.ToString(), CharUtil.GetExcelCharByNumber(17) + (followupInfoList.Count - 1 + rowNum).ToString());
              rg2.Merge(0);
              rg2.Cells[1, 1] = (pInfo.Height == 0) ? 0 : pInfo.Weight / (pInfo.Height * pInfo.Height);

              for (int i = 0; i < followupInfoList.Count; i++)
              {
                  string diagnoseText = "";
                  List<DiagnoseGroup> diagnoseList = followupInfoList[i].DiagnoseList;
                  if (diagnoseList != null)
                  {
                      for (int k = 0; k < diagnoseList.Count; k++)
                      {
                          diagnoseText += diagnoseList[k].Diagnose + ";";
                      }
                  }
                  worksheet.Cells[i + rowNum, 18] = diagnoseText;
                  worksheet.Cells[i + rowNum, 19] = followupInfoList[i].FollowupDate;
                  worksheet.Cells[i + rowNum, 20] = followupInfoList[i].FollowupTimes;
                  worksheet.Cells[i + rowNum, 21] = followupInfoList[i].FollowUper;

                  worksheet.Cells[i + rowNum, 22] = followupInfoList[i].BriefHistory;
                  worksheet.Cells[i + rowNum, 23] = followupInfoList[i].BloodPressure;
                  worksheet.Cells[i + rowNum, 24] = followupInfoList[i].Heatrate;
                  worksheet.Cells[i + rowNum, 25] = followupInfoList[i].Breath;
                  worksheet.Cells[i + rowNum, 26] = followupInfoList[i].Temperature;
                  worksheet.Cells[i + rowNum, 27] = followupInfoList[i].Treatment;
                  worksheet.Cells[i + rowNum, 28] = followupInfoList[i].Other;
                  worksheet.Cells[i + rowNum, 29] = followupInfoList[i].PathologyDiagnose;
                  worksheet.Cells[i + rowNum, 30] = followupInfoList[i].PathologyClassification;
                  worksheet.Cells[i + rowNum, 31] = followupInfoList[i].PathologyLevel;
                  worksheet.Cells[i + rowNum, 32] = followupInfoList[i].PathologyPeriod;

                  //填充检验检查信息
                  startindex = _followupInfoColumns.Length + _patientInfoColumns.Length + _checkInfoColums.Length;
                  ObservableCollection<ExamItemValue> itemValues = DataServiceManager.ItemValueService.GetExamItemValueBySourceId(followupInfoList[i].Id);
                 
                  for (int k = 0; k < itemValues.Count; k++)
                  {
                      if (itemIdList.IndexOf(itemValues[k].ItemId) < 0)
                          continue;
                      ObservableCollection<IndiceKVP> kvps = itemValues[k].IndiceKvps;
                      for (int m = 0; m < kvps.Count; m++)
                      {
                          int clmNo = 0;
                          foreach (var dictitem in dictIndiceClmNo)
                          {
                              if (dictitem.Key == itemValues[k].ItemId + kvps[m].Key)
                              {
                                  clmNo = dictitem.Value;
                                  break;
                              }
                          }
                          worksheet.Cells[i + rowNum, clmNo] = kvps[m].Value;
                      }                        
                  }
              }           
              rowNum += followupInfoList.Count + 1;
              no ++;
          }
          xlApp.Visible = true;
   
            //另存excel
            workbook.Saved = true;
            workbook.SaveCopyAs(filePath);
        }

        /**
         * 功能：添加合并的表头
         * 
         * */
        private static void AddMergedTableHeader(Worksheet worksheet, string title, int columnStartNo, int columnEndNo, int rowNo)
        {
            Range rgExamInfo = worksheet.get_Range(CharUtil.GetExcelCharByNumber(columnStartNo) + rowNo.ToString(), CharUtil.GetExcelCharByNumber(columnEndNo) + rowNo.ToString());
            rgExamInfo.Merge(0);
            rgExamInfo.Interior.ColorIndex = 20;
            rgExamInfo.Font.Bold = true;
            rgExamInfo.Font.Color = ConsoleColor.Blue;
            rgExamInfo.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rgExamInfo.RowHeight = 15;
            rgExamInfo.Cells[1, 1] = title;
        }
    }
}
